package com.neusoft.chenhanhua.gqing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;
import org.junit.internal.matchers.SubstringMatcher;

public class Cms_prefrence_area_product_relation {

	@Test
	public void insert() {
		Connection conn=null;
		//准备数据库用户名，密码，url,driver
		String username="root";//用户名
		String password="root";//密码
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			//加载到驱动类
			Class.forName(driver);
			//获取连接
			conn=DriverManager.getConnection(url,password,username);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备sql命令
		String sql="insert into cms_prefrence_area_product_relation(id,prefrence_area_id,product_id)values(8,2,3)";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			 st=conn.createStatement();
			//执行sql命令
			int row =st.executeUpdate(sql);
			System.out.println(row>0?"添加成功":"添加失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test
	public void update() {
		Connection conn=null;
		//准备数据库用户名，密码，url,driver
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&characterEncoding=utf-8";
		try {
			Class.forName(driver);
			//连接数据库
			conn=DriverManager.getConnection(url,password,username);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="update Cms_prefrence_area_product_relation set prefrence_area_id=4 where id=2";
		Statement st=null;
		try {
			//通过数据库连接conn获取事务java.sql.statement对象st
			st=conn.createStatement();
			//执行sql命令
			int row=st.executeUpdate(sql);
			System.out.println(row>0?"修改成功":"修改失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	} 
	@Test
	public void delet() {
		Connection connection=null;
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode=true&charaterEncoding=utf-8";
		try {
			//加载驱动类到虚拟机
			Class.forName(driver);
			connection =DriverManager.getConnection(url,password,username);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="delete from Cms_prefrence_area_product_relation where id=3";
		Statement statement=null;
		try {
			//通过数据库连接conn获取事务java.sql.Statement对象st
			statement=connection.createStatement();
			int row=statement.executeUpdate(sql);
			System.out.println(row>0?"删除成功":"删除失败");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (statement!=null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (connection!=null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	@Test 
	public void stelet() {
		Connection con=null;
		String username="root";
		String password="root";
		String driver="com.mysql.jdbc.Driver";
		String url="jdbc:mysql://localhost:3306/mall?useUnicode+true&characterEncoding=utf-8";
		try {
			//加载到虚拟机中
			Class.forName(driver);
			//获取数据库连接java.sql.connection
			con=DriverManager.getConnection(url,username,password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		//准备SQL命令
		String sql="select * from Cms_prefrence_area_product_relation";
		Statement st=null;
		ResultSet rs = null;
		try {
			st=con.createStatement();
		     rs=st.executeQuery(sql);
			//处理查询结果集
			while(rs.next()) {
				//rs.getInt(columnIndex)：根据索引获取列的值，强烈不推荐
				//获取id列的值   id,prefrence_area_id,product_id
				//System.out.println(rs.getInt(1));
				//rs.getInt(columnLabel)：columnLabel列名或列的别名，推荐使用
				System.out.println(rs.getInt("id"));
				System.out.println(rs.getInt("prefrence_area_id"));
				System.out.println(rs.getInt("product_id"));
				//获取username列
				//System.out.println(rs.getString("username"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (con!=null) {
				try {
					con.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (st!=null) {
				try {
					st.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}
